SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE View [dbo].[TDMOVI_BANF]  /* Detalle Movs Bancarios, agrup por Tipo de Operacion */
As  
Select	t1.CO_EMPR As CO_EMPR,  
	t1.CO_BANC As CO_BANC,  
	t1.NU_CNTA_BANC  As  NU_CNTA_BANC,  
	t1.AA_BNCO  As  AA_BNCO,  
	t1.MM_BNCO  As  MM_BNCO,  
	t1.TI_MOVI_BANC  As  TI_MOVI_BANC,  
	t1.NU_COMP_BANC  As  NU_COMP_BANC,  
	t2.TI_DOCU_ENTI AS TI_DOCU_ENTI,  t2.NU_DOCU_ENTI As NU_DOCU_ENTI,  
	t2.CO_MONE_DOCU As CO_MONE_DOCU, t2.FE_DOCU As FE_DOCU,  
	
	t2.IM_DETA - (dbo.FN_TCFACT_CAMB_Q02(t4.CO_MONE_DEFA, t2.CO_MONE_DOCU, t1.FE_OPER,   
	t5.IM_RETE_CONV, t4.TI_CAMB, t4.TI_ORIG, 'S', t4.CO_MONE_DEFA, t4.CO_MONE_EXTR,   
	t1.FA_TIPO_CAMB)) As IM_DOCU,   
	
	t2.NU_SECU As NU_SECU  
From	TCMOVI_BANC t1, TDMOVI_BANC t2,  
	TMPARA_TESO t4,  
	TDCOMP_RETE t5 , TCCOMP_RETE t6  
Where  t1.CO_EMPR = t2.CO_EMPR  
And t1.CO_BANC  = t2.CO_BANC  
And t1.NU_CNTA_BANC = t2.NU_CNTA_BANC  
And t1.AA_BNCO = t2.AA_BNCO  
And t1.MM_BNCO = t2.MM_BNCO  
And t1.TI_MOVI_BANC = t2.TI_MOVI_BANC  
And t1.NU_COMP_BANC = t2.NU_COMP_BANC  
And ( Len(Rtrim(Ltrim(t2.TI_DOCU_ENTI)))> 0 And Len(Rtrim(Ltrim(t2.NU_DOCU_ENTI)))> 0 )   
  
And Exists ( Select t3.CO_EMPR  
      From TTTIPO_OPER  t3  
      Where t1.CO_EMPR = t3.CO_EMPR  
  And t1.CO_TIPO_OPER = t3.CO_TIPO_OPER  
  And t3.ST_DETA_DOCU = 'S' )  
And t2.CO_EMPR = t4.CO_EMPR  
  
And t6.CO_EMPR = t5.CO_EMPR  
And t6.NU_COMP_RETE = t5.NU_COMP_RETE  
And t6.TI_SITU != 'ANU'  
  
And t2.CO_EMPR = t5.CO_EMPR And   
t2.TI_DOCU_ENTI = t5.TI_DOCU And   
t2.NU_DOCU_ENTI = t5.NU_DOCU   
  
And t2.NU_CNTA_BANC = t6.NU_CNTA_BANC  
And t2.AA_BNCO = t6.AA_BACA  
And t2.MM_BNCO = t6.MM_BACA  
   
Union All  
    
Select	t1.CO_EMPR ,  
	t1.CO_BANC ,  
	t1.NU_CNTA_BANC  ,  
	t1.AA_BNCO  ,  
	t1.MM_BNCO  ,  
	t1.TI_MOVI_BANC ,  
	t1.NU_COMP_BANC ,  
	t2.TI_DOCU_ENTI ,  t2.NU_DOCU_ENTI ,  
	t2.CO_MONE_DOCU , t2.FE_DOCU ,  
	t2.IM_DETA,   
	t2.NU_SECU  
From	TCMOVI_BANC t1, TDMOVI_BANC t2  
Where  t1.CO_EMPR = t2.CO_EMPR  
And t1.CO_BANC  = t2.CO_BANC  
And t1.NU_CNTA_BANC = t2.NU_CNTA_BANC  
And t1.AA_BNCO = t2.AA_BNCO  
And t1.MM_BNCO = t2.MM_BNCO  
And t1.TI_MOVI_BANC = t2.TI_MOVI_BANC  
And t1.NU_COMP_BANC = t2.NU_COMP_BANC  
And ( Len(Rtrim(Ltrim(t2.TI_DOCU_ENTI)))> 0 And Len(Rtrim(Ltrim(t2.NU_DOCU_ENTI)))> 0 )   
And Exists ( Select t3.CO_EMPR  
      From TTTIPO_OPER  t3  
      Where t1.CO_EMPR = t3.CO_EMPR  
  And t1.CO_TIPO_OPER = t3.CO_TIPO_OPER  
  And t3.ST_DETA_DOCU = 'S' )  
And Not Exists ( Select t5.CO_EMPR  
  From   TDCOMP_RETE t5, TCCOMP_RETE t6  
  Where  t5.CO_EMPR = t2.CO_EMPR And   
  t5.TI_DOCU = t2.TI_DOCU_ENTI And   
  t5.NU_DOCU = t2.NU_DOCU_ENTI  
  
  And t6.CO_EMPR = t5.CO_EMPR  
  And t6.NU_COMP_RETE = t5.NU_COMP_RETE  
  And t6.TI_SITU != 'ANU'   
  
  And t2.NU_CNTA_BANC = t6.NU_CNTA_BANC  
  And t2.AA_BNCO = t6.AA_BACA  
  And t2.MM_BNCO = t6.MM_BACA  
   )  
Union All  
  
Select	t1.CO_EMPR,  
	t1.CO_BANC,  
	t1.NU_CNTA_BANC,  
	t1.AA_BNCO,  
	t1.MM_BNCO,  
	t1.TI_MOVI_BANC,  
	t1.NU_COMP_BANC,  
	(t2.AA_CAJA_DEST + '-' + t2.MM_CAJA_DEST),  t2.CO_CAJA_DEST,  
	Null, t2.FE_OPER,  
	t2.IM_MOVI,   
	t2.NU_SECU  
From	TCMOVI_BANC t1,  
	TDMOVI_RECA t2  
Where  t1.CO_EMPR = t2.CO_EMPR  
And t1.CO_BANC  = t2.CO_BANC  
And t1.NU_CNTA_BANC = t2.NU_CNTA_BANC  
And t1.AA_BNCO = t2.AA_BNCO  
And t1.MM_BNCO = t2.MM_BNCO  
And t1.TI_MOVI_BANC = t2.TI_MOVI_BANC  
And t1.NU_COMP_BANC = t2.NU_COMP_BANC  
And  Len(Rtrim(Ltrim(t2.CO_CAJA_DEST)))> 0  
And Exists ( Select t3.CO_EMPR  
      From TTTIPO_OPER  t3  
      Where t1.CO_EMPR = t3.CO_EMPR  
  And t1.CO_TIPO_OPER = t3.CO_TIPO_OPER  
  And t3.ST_REPO_CAJA = 'S' )  
  
Union All  
  
Select	t1.CO_EMPR,  
	t1.CO_BANC,  
	t1.NU_CNTA_BANC,  
	t1.AA_BNCO,  
	t1.MM_BNCO,  
	t1.TI_MOVI_BANC,  
	t1.NU_COMP_BANC,  
	IsNull(t2.CO_TIPO_DOCU,'S/d'),  t2.NU_DOCU_BANC,  
	(t2.CO_BANC + '-' + t2.NU_CNTA_BANC), t2.FE_OPER,  
	t2.IM_MOVI,   
	t2.NU_SECU  
From	TCMOVI_BANC t1,  
	TDMOVI_TRBA t2  
Where  t1.CO_EMPR = t2.CO_EMPR  
And t1.CO_BANC  = t2.CO_BANC  
And t1.NU_CNTA_BANC = t2.NU_CNTA_BANC  
And t1.AA_BNCO = t2.AA_BNCO  
And t1.MM_BNCO = t2.MM_BNCO  
And t1.TI_MOVI_BANC = t2.TI_MOVI_BANC  
And t1.NU_COMP_BANC = t2.NU_COMP_BANC  
And Exists ( Select t3.CO_EMPR  
      From TTTIPO_OPER  t3  
      Where t1.CO_EMPR = t3.CO_EMPR  
  And t1.CO_TIPO_OPER = t3.CO_TIPO_OPER  
  And t3.ST_TRAN_BANC = 'S' )  
  
  
Union All  
  
  
Select	t1.CO_EMPR,  
	t1.CO_BANC,  
	t1.NU_CNTA_BANC,  
	t1.AA_BNCO,  
	t1.MM_BNCO,  
	t1.TI_MOVI_BANC,  
	t1.NU_COMP_BANC,  
	t2.TI_MOVI, t2.CO_CNTA_EMPR,  
	(IsNull(t2.CO_TIPO_AUXI,'') + '-' + IsNull(t2.CO_AUXI_EMPR,'') + '-' + IsNull(t2.CO_ORDE_SERV,'')), t1.FE_OPER,  
	t2.IM_DIST,   
	t2.NU_SECU  
From	TCMOVI_BANC t1,  
	TDBANC_DIST t2  
Where  t1.CO_EMPR = t2.CO_EMPR  
And t1.CO_BANC  = t2.CO_BANC  
And t1.NU_CNTA_BANC = t2.NU_CNTA_BANC  
And t1.AA_BNCO = t2.AA_BNCO  
And t1.MM_BNCO = t2.MM_BNCO  
And t1.TI_MOVI_BANC = t2.TI_MOVI_BANC  
And t1.NU_COMP_BANC = t2.NU_COMP_BANC  
And Exists ( Select t3.CO_EMPR  
      From TTTIPO_OPER  t3  
      Where t1.CO_EMPR = t3.CO_EMPR  
  And t1.CO_TIPO_OPER = t3.CO_TIPO_OPER  
  And t3.ST_AUXI_0003 = 'S' )

GO
